Oracle Fusion - SQL Query for Requisitions
|
2 min read
General
This is an Oracle Fusion SQL Query returning the data from Requisitions - Lines and distributions.
The entities joined are:
- Project
- Project Task
- Vendors
- Person Name
- Suppliers - Profile and Site
Query
SELECT
HOU.NAME AS "Operating Unit",
PRH.REQUISITION_NUMBER AS "Requisition Number",
PRH.DESCRIPTION AS "Description",
PRH.DOCUMENT_SUB_TYPE AS "Requisition Type",
PRH.DOCUMENT_STATUS AS "Requisition Status",
PRL.currency_code AS "Requisition Currency",
PRL.LINE_NUMBER AS "Requisition Line Num",
PRL.ITEM_DESCRIPTION AS "Requisition Line Desc",
PRL.UOM_CODE AS "UOM",
PRL.QUANTITY AS "Quantity",
PRL.UNIT_PRICE AS "Unit Cost",
MSI.item_number AS "Item Number",
PRL.LIFECYCLE_STATUS "Line Lifecycle Status",
PRL.LINE_STATUS as "Line Status",
PRL.FUNDS_STATUS AS "Line Funds Status",
PRL.VENDOR_ID,
HP.PARTY_NAME as "Vendor",
PRL.VENDOR_SITE_ID,
PSSV.PARTY_SITE_NAME as "Vendor Site",
HOUT.NAME AS "Organization",
HLA.LOCATION_CODE AS "Location",
PRL.DESTINATION_SUBINVENTORY AS "Department",
per_name.display_name AS "Preparer",
per_name2.display_name AS "Requisitioner",
PRD.distribution_number "Distribution Number",
PRD.percent "Distribution Percent",
PRD.distribution_currency_amount "Distribution Amount Currency",
PRD.distribution_amount "Distribution Amount",
PRD.funds_status as "Distribution Funds Status",
PRJ.SEGMENT1 "Project Number",
PRJT.NAME AS "Project Name",
PRJ.PROJECT_CURRENCY_CODE "Project Currency",
PRJ_TASK.ELEMENT_NUMBER AS "Project Task Number",
PRJT_TASK.NAME AS "Project Task Name"
FROM
POR_REQUISITION_HEADERS_ALL PRH,
POR_REQUISITION_LINES_ALL PRL,
POR_REQ_DISTRIBUTIONS_ALL PRD,
PER_ALL_PEOPLE_F PPF,
PER_ALL_PEOPLE_F PPF2,
per_persons per,
per_person_names_f per_name,
per_persons per2,
per_person_names_f per_name2,
EGP_SYSTEM_MASTER_ITEMS_VL MSI,
HR_LOCATIONS_ALL HLA,
HR_ALL_ORGANIZATION_UNITS HOUT,
HR_ALL_ORGANIZATION_UNITS HOU,
PJF_PROJ_ELEMENTS_B PRJ_TASK,
PJF_PROJ_ELEMENTS_TL PRJT_TASK,
PJF_PROJECTS_ALL_B PRJ,
PJF_PROJECTS_ALL_TL PRJT,
HZ_PARTIES HP,
POZ_SUPPLIER_SITES_V PSSV,
POZ_SITE_ASSIGNMENTS_ALL_M PSAAM,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
POZ_SUPPLIERS PS
WHERE
1 = 1
AND NVL(PRL.CANCEL_FLAG, 'N') <> 'Y'
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.requisition_line_id = PRD.requisition_line_id
AND TRUNC(SYSDATE) BETWEEN nvl(PPF.EFFECTIVE_START_DATE, sysdate - 1)
AND nvl(PPF.EFFECTIVE_END_DATE, sysdate + 1)
AND TRUNC(SYSDATE) BETWEEN nvl(PPF2.EFFECTIVE_START_DATE, sysdate - 1)
AND nvl(PPF2.EFFECTIVE_END_DATE, sysdate + 1)
AND PPF.PERSON_ID = PRH.PREPARER_ID
AND PPF2.PERSON_ID = PRL.REQUESTER_ID
AND per.person_id = PPF.person_id
AND per_name.person_id = PPF2.person_id
AND per2.person_id = PPF.person_id
AND per_name2.person_id = PPF2.person_id
and per_name2.name_type = 'GLOBAL'
and per_name.name_type = 'GLOBAL'
AND MSI.INVENTORY_ITEM_ID(+) = PRL.ITEM_ID
AND HLA.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
AND HOUT.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = PRh.REQ_BU_ID
AND PRJ.PROJECT_ID(+) = PRD.pjc_project_id
AND PRJ_TASK.PROJ_ELEMENT_ID(+) = PRD.pjc_task_id
AND PRJT_TASK.PROJ_ELEMENT_ID(+) = PRJ_TASK.PROJ_ELEMENT_ID
AND USERENV('LANG') = PRJT_TASK.LANGUAGE(+)
AND USERENV('LANG') = PRJT.LANGUAGE(+)
AND PRJT.PROJECT_ID(+) = PRJ.PROJECT_ID
AND PS.PARTY_ID = HP.PARTY_ID(+)
AND PRL.VENDOR_ID = PSSV.VENDOR_ID(+)
AND PRL.VENDOR_SITE_ID = PSSV.VENDOR_SITE_ID(+)
AND PSSV.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID(+)
AND PSSAM.VENDOR_SITE_ID = PSAAM.VENDOR_SITE_ID(+)
AND PS.VENDOR_ID(+) = PRL.VENDOR_ID